<?php defined('SYSPATH') or die('No direct script access.');
                          
class Bookstore_Controller extends Template_Controller 
{
	public $template = 'layout/bookstore';
	
	public function __construct()
	{
		parent::__construct();
		
		$this->db = new Database();

		$this->template->highlight_id = '';
		$this->template->menu = '';
		$this->template->content = '';
		$this->template->leftmenu = $this->db->query('SELECT * FROM product_categories WHERE left_id>1 AND right_id <(SELECT right_id FROM product_categories WHERE id=1) AND layer_id<=2 ORDER BY left_id');
	}
	
	public function index()
	{
		$model = new Product_Model();
		$article = new Article_Model();
		$view = new View('bookstore/home');
		$view->new_products = Database::instance()->query("SELECT products.* FROM products JOIN block_elements ON block_elements.source_pk_id=products.id WHERE block_elements.block_id=6 ORDER BY sort");
		$view->best_products = Database::instance()->query("SELECT products.* FROM products JOIN block_elements ON block_elements.source_pk_id=products.id WHERE block_elements.block_id=7 ORDER BY sort");
		$view->onsale = Database::instance()->query("SELECT products.* FROM products JOIN block_elements ON block_elements.source_pk_id=products.id WHERE block_elements.block_id=8 ORDER BY sort");
		$this->template->content =  $view->render();
	}
		
	public function category($category_id, $page_no)
	{
		$page_size = 28;
		
		$cat = new Product_Category_Model($category_id);
		
		$model = new Product_Model();
		
		$this->pagination = new Pagination(array(
	        'base_url'    => 'bookstore/category/'.$category_id, 
	        'uri_segment' => 4, 
			'items_per_page' => $page_size, 
	        'total_items' => count($model->select('DISTINCT products.*')
					   ->join('products_categories AS pc', 'pc.product_id=products.id')
					   ->where('pc.category_id', $category_id)
					   ->orwhere('pc.category_id in (select id from product_categories where left_id BETWEEN '.$cat->left_id.' and '.$cat->right_id.')')
					   ->orderby('updated_at', 'DESC')->find_all())
	    ));
		
		$this->category_id = $category_id;
		
		$view = new View('bookstore/category');
		$view->products = $model->select('DISTINCT products.*')
					   ->join('products_categories AS pc', 'pc.product_id=products.id')
					   ->where('pc.category_id', $category_id)
					   ->orwhere('pc.category_id in (select id from product_categories where left_id BETWEEN '.$cat->left_id.' and '.$cat->right_id.')')
					   ->orderby('title_'.(cookie::get('lang') ? cookie::get('lang') : 'cn'), 'ASC')->limit($page_size, (($page_no - 1) * $page_size))->find_all();
		
		$view->navigator = $this->db->query("select * from product_categories as pc where left_id <= ".$cat->left_id.' and right_id >= '.$cat->right_id.' and parent_id<>0 order by left_id asc;');
		$view->featured = $this->db->query("select p.* from products as p, products_categories as pc where p.id=pc.product_id and pc.featured=1 and pc.category_id=".$category_id);
		//$view->featured = $this->db->query("select p.* from products as p, products_categories as pc where p.id=pc.product_id and pc.featured=1 and pc.category_id in (select id from product_categories where left_id BETWEEN ".$cat->left_id." and ".$cat->right_id.")");
		
		foreach($view->navigator as $item)
		{
			if($item->layer_id==1)
			{
				$this->template->highlight_id =  $item->id;
				break;
			}
		}
		
		$this->template->content =  $view->render();
	}
	
	public function newbooks($page_no)
	{
		$page_size = 28;
		$model = new Product_Model();
		
		$this->pagination = new Pagination(array(
	        'base_url'    => 'bookstore/newbooks/', 
	        'uri_segment' => 3, 
			'items_per_page' => $page_size, 
	        'total_items' => $model->select('COUNT(*) AS records_found')
								   ->orderby('updated_at', 'DESC')
								   ->find()->records_found
	    ));
		
		$view = new View('bookstore/newbooks');
		$view->products = $model->orderby('updated_at', 'DESC')
								->limit($page_size, (($page_no - 1) * $page_size))->find_all();
		$this->template->content =  $view->render();
	}
	
	public function bestsellers($page_no)
	{
		$page_size = 28;
		$model = new Product_Model();
		
		$this->pagination = new Pagination(array(
	        'base_url'    => 'bookstore/bestsellers/', 
	        'uri_segment' => 3, 
			'items_per_page' => $page_size, 
	        'total_items' => $model->select('COUNT(*) AS records_found')
								   ->find()->records_found
	    ));
		
		$view = new View('bookstore/bestsellers');
		$view->products = $model->orderby('total_sold', 'DESC')->orderby('updated_at', 'DESC')
								->limit($page_size, (($page_no - 1) * $page_size))->find_all();
		$this->template->content =  $view->render();
	}
	
	public function product($category_id, $product_id)
	{
		$view = new View('bookstore/product');
		$view->category = new Product_Category_Model($category_id);
		$view->product = new Product_Model($product_id);
		$view->related = $this->db->query("SELECT * FROM products Inner Join products_categories ON products_categories.product_id = products.id WHERE products_categories.category_id = '".$category_id."' AND products.id<>".$product_id." ORDER BY rand() LIMIT 8;");
		$this->template->content =  $view->render();
	}
	
	public function search($keyword, $page_no)
	{
		$page_size = 28;
		$q = $keyword ? $keyword : '';
		$model = new Product_Model();
		
		$like = array( 
			'title_cn'=>$q, 
			'title_en'=>$q, 
			'description_cn'=>$q, 
			'description_en'=>$q, 
			'details_cn'=>$q, 
			'details_en'=>$q, 
			'sku'=>$q, 
			'author_cn'=>$q, 
			'author_en'=>$q, 
			'publisher_cn'=>$q, 
			'publisher_en'=>$q
		);
		
		$this->pagination = new Pagination(array(
	        'base_url'    => 'bookstore/search/'.$q.'/1', 
	        'uri_segment' => 4, 
			'items_per_page' => $page_size, 
	        'total_items' => $model->select('DISTINCT COUNT(*) AS records_found')
								   ->orlike($like)
								   ->find()->records_found
	    ));
		
		$view = new View('bookstore/search');
		$view->products = $model->orlike($like)
								->orderby('title_'.cookie::get('lang'), 'ASC')
								->limit($page_size, (($page_no - 1) * $page_size))->find_all();
		
		$this->template->qKeyword = $q;				
		$this->template->content = $view->render();
	}
	
	public function import1()
	{
		$str1 = '';
		$str2 = '';
		$db = new Database();
		
		$r1 = $db->query('SELECT * FROM existing_titles');
		
		foreach($r1 as $r)
		{
			if( empty($r->titile_cn) and empty($r->title_en) ) continue;
			
			$s = new Product_Model();
			
			$has = $s->where('title_cn', $r->title_cn)->where('title_en', $r->title_en)->find();
			
			if($has) $s = new Product_Model($s->id);
			
			$s->series = $r->series;
			$s->title_cn = str_replace("\n", '', $r->title_cn);
			$s->title_en = str_replace("\n", '', $r->title_en);
			$s->description_cn = $r->desc_cn;
			$s->description_en = $r->desc_en;
			$s->author_cn = $r->author_cn;
			$s->author_en = $r->author_en;
			$s->publisher_cn = $r->publisher_cn;
			$s->publisher_en = $r->publisher_en;
			$s->quantity = $r->Quantity;
			$s->weight = $r->Weight;
			$s->sku = $r->SKU;
			//$s->amount = $r->amount;
			$s->market_price = $r->Price;
			$s->price = $r->Price;
			$s->extra_handling = $r->extra;
			//$s->photo_ext = ( ! empty($r->photo1) and file_exists('D:/nanhai/photo/all/'.$r->photo1) ) ? strtolower(file::extension($r->photo1)) : '';
						
			if($s->save())
			{			
				if( ! empty($r->category) )
				{
					$cat = explode('&', $r->category);
					
					foreach($cat as $c)
					{
						$c = str_replace(array('LL-', 'HC-', 'LLi'), array('', '', ''), $c);
						$c = trim($c);
	
						if($su = $db->query("select id from product_categories where title_en='".$c."' or title_cn='".$c."'"))
						{
							$n = 1;
							foreach($su as $item)
							{
								if($n == 1) 
								{
									$s->category_id = $item->id;
									$s->save();
									$n++;
								}
								
								$db->query('REPLACE into products_categories set category_id='.$item->id.', product_id='.$s->id);
							}
						}
					}
				}
				
				//if( ! empty($r->photo1) and file_exists('D:/nanhai/photo/all/'.$r->photo1) )
				//{
				//	$img = new Image('D:/nanhai/photo/all/'.$r->photo1);
				//	$img->save('D:/Aptana Studio/phproot/nanhai.com/resource/product/'.$s->id.'.'.$s->photo_ext);
				//}
			}
		}
	}
	
	public function clean()
	{
		$m = new Existing_Titles_Textbook_Model();
		
		$s = $m->find_all();
		
		echo '<table border="1">';
		
		foreach($s as $r)
		{
			$m1 = new Existing_Titles_Textbook_Model($r->id);
			
			$m1->title_cn = str_replace("\n", '', $r->title_cn);
			$m1->title_en = str_replace("\n", '', $r->title_en);
			
			$m1->save();
			
			echo '<tr><td>'.$r->id.'</td>';
			echo '<td>'.nl2br($m1->title_cn).'</td>';
			echo '<td>'.nl2br($r->title_cn).'</td></tr>';
			
		}
		echo '</table>';
		exit;
	}
	
	public function image()
	{
		$db = new Database();
		
		$tables = array('existing_titles', 'existing_titles_children', 'existing_titles_medicine', 'existing_titles_others', 'existing_titles_parent', 'existing_titles_social');
		
		foreach($tables as $table)
		{
			$result = $db->query('select p.id, e.photo1 from products p, '.$table.' e where p.title_cn=e.title_cn;');
			
			foreach($result as $item)
			{
				if( ! empty($item->photo1) and file_exists('D:/nanhai/photo/'.$item->photo1) )
				{
					$s = new Product_Model($item->id);
					$s->photo_ext = strtolower(file::extension($item->photo1));
					$s->save();
					$img = new Image('D:/nanhai/photo/'.$item->photo1);
					$img->save('D:/Aptana Studio/phproot/nanhai.com/resource/product/'.$item->id.'.'.$s->photo_ext);
					$img->save('D:/nanhai/photo/ok/'.$item->id.'.'.$s->photo_ext);
					unlink('D:/nanhai/photo/'.$item->photo1);
				}
			}
		}
	}
	
	public function photo()
	{
		$db = new Database();
		
		$result = $db->query("select p.id, p.title_cn, e.photo1, e.id as eid from products p left join  existing_alls e on p.title_cn=e.title_cn where (p.photo_ext=' ' or p.photo_ext is null);");
		
		echo '<table border="1">';
		foreach($result as $item)
		{
			echo '<tr><td>'.$item->id.'</td>';
			echo '<td>'.$item->title_cn.'</td>';
			echo '<td>'.$item->photo1.'</td>';
			echo '<td>'.$item->eid.'</td></tr>';
			
			if( ! empty($item->photo1) and file_exists('D:/nanhai/photo/all/'.$item->photo1) )
			{
				$s = new Product_Model($item->id);
				$s->photo_ext = strtolower(file::extension($item->photo1));
				$s->save();
				$img = new Image('D:/nanhai/photo/all/'.$item->photo1);
				$img->save('D:/Aptana Studio/phproot/nanhai.com/resource/product/'.$item->id.'.'.$s->photo_ext);
			}
		}
		echo '</table>';
		exit;
	}
	
	public function photo1()
	{
		$db = new Database();
		
		$result = $db->query("select p.id, p.title_cn, e.title_cn as old_title_cn, e.id as eid from products p left join  products_old e on p.title_cn=e.title_cn where (p.photo_ext=' ' or p.photo_ext is null);");
		
		echo '<table border="1">';
		foreach($result as $item)
		{
			echo '<tr><td>'.$item->id.'</td>';
			echo '<td>'.$item->title_cn.'</td>';
			echo '<td>'.$item->old_title_cn.'</td>';
			echo '<td>'.$item->eid.'</td></tr>';
			
			if( ! empty($item->eid) and file_exists('D:/Aptana Studio/phproot/nanhai.com/resource/product1/'.$item->eid.'.jpg') )
			{
				$s = new Product_Model($item->id);
				$s->photo_ext = 'jpg';
				$s->save();
				$img = new Image('D:/Aptana Studio/phproot/nanhai.com/resource/product1/'.$item->eid.'.jpg');
				$img->save('D:/Aptana Studio/phproot/nanhai.com/resource/product/'.$item->id.'.jpg');
			}
		}
		echo '</table>';
		exit;
	}
}